const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 实体类映射表
const entityMappings = {
  'UserEntity': 'users',
  'CharacterEntity': 'characters',
  'CharacterAttributeEntity': 'character_attributes',
  'CharacterDetailedAttributeEntity': 'character_detailed_attributes',
  'CharacterSkillEntity': 'character_skills',
  'SocialRelationEntity': 'social_relations',
  'CharacterReligionEntity': 'character_religions',
  'CharacterResidenceEntity': 'character_residences',
  'CharacterStoneEntity': 'character_stones',
  'CharacterResourcesEntity': 'character_resources',
  'CharacterInventoryEntity': 'character_inventory',
  'CharacterEquipmentEntity': 'character_equipment',
  'CharacterEquipmentInventoryEntity': 'character_equipment_inventory',
  'CharacterGemInventoryEntity': 'character_gem_inventory',
  'EquipmentGemSocketEntity': 'equipment_gem_socket',
  'CharacterAttributePointsEntity': 'character_attribute_points',
  'CharacterLocationEntity': 'character_locations',
  'CharacterMountEntity': 'character_mounts',
  'MountTrainingEntity': 'mount_training',
  'CharacterTrainingEntity': 'character_training',
  'CharacterMedicineEntity': 'character_medicine',
  'MonthlyCardEntity': 'monthly_cards',
  'ChatMessageEntity': 'chat_messages',
  'PrivateMessageEntity': 'private_messages',
  'FactionEntity': 'factions',
  'FactionMemberEntity': 'faction_members',
  'DeputyGeneral': 'deputy_general',
  'DeputyGeneralBase': 'deputy_general_base',
  'DeputyGeneralSkillLearned': 'deputy_general_skill_learned',
  'DeputyGeneralEquipment': 'deputy_general_equipment',
  'DeputyGeneralStone': 'deputy_general_stone',
  'PlayerDeputy': 'player_deputy',
  'PlayerDeputyWarehouse': 'player_deputy_warehouse',
  'GemBaseEntity': 'gem_base',
  'GemAttributeEntity': 'gem_attribute',
  'GemMiningEntity': 'gem_mining',
  'TalentStoneEntity': 'talent_stone',
  'TalentStoneBaseEntity': 'talent_stone_base',
  'TalentStoneLevelEntity': 'talent_stone_level',
  'PlayerOnlineTimeEntity': 'player_online_time',
  'NpcClusterEntity': 'npc_clusters',
  'NpcDetailEntity': 'npc_details',
  'BasicItemEntity': 'basic_items',
  'EquipmentBaseEntity': 'equipment_base',
  'MountBaseEntity': 'mount_base',
  'MountLevelExperienceEntity': 'mount_level_experience',
  'SkillBase': 'skill_base',
  'LevelExperienceEntity': 'level_experience',
  'MapAreaEntity': 'map_areas',
  'AreaEntity': 'areas',
  'AnnouncementEntity': 'announcements',
  'ItemEntity': 'items',
  'SystemMessageEntity': 'system_messages',
  'ShopGoodsEntity': 'shop_goods'
};

async function checkEntityTableMapping() {
  let connection;
  
  try {
    connection = await mysql.createConnection({
      host: '43.143.253.188',
      port: 3306,
      user: 'nextjs',
      password: 'yEAiRE2jcxTyRBy5',
      database: 'nextjs'
    });

    console.log('✅ 数据库连接成功');
    console.log('🔍 开始检测实体类与数据库表的对应关系...\n');

    // 获取数据库中的所有表
    const [tables] = await connection.execute('SHOW TABLES');
    const dbTables = tables.map(row => Object.values(row)[0]);
    
    console.log(`📊 数据库中共有 ${dbTables.length} 个表`);
    console.log(`📊 实体类映射中共有 ${Object.keys(entityMappings).length} 个实体类\n`);

    // 检查实体类对应的表是否存在
    console.log('🔍 检查实体类对应的表是否存在:');
    const missingTables = [];
    const existingTables = [];
    
    for (const [entityName, tableName] of Object.entries(entityMappings)) {
      if (dbTables.includes(tableName)) {
        existingTables.push({ entity: entityName, table: tableName });
        console.log(`✅ ${entityName} -> ${tableName}`);
      } else {
        missingTables.push({ entity: entityName, table: tableName });
        console.log(`❌ ${entityName} -> ${tableName} (表不存在)`);
      }
    }

    // 检查数据库中存在但实体类中没有的表
    console.log('\n🔍 检查数据库中存在但实体类中没有的表:');
    const extraTables = [];
    for (const dbTable of dbTables) {
      const found = Object.values(entityMappings).includes(dbTable);
      if (!found) {
        extraTables.push(dbTable);
        console.log(`⚠️  ${dbTable} (数据库中存在但实体类中没有)`);
      }
    }

    // 统计结果
    console.log('\n📊 检测结果统计:');
    console.log(`   ✅ 实体类与表对应正常: ${existingTables.length} 个`);
    console.log(`   ❌ 实体类对应的表缺失: ${missingTables.length} 个`);
    console.log(`   ⚠️  数据库中存在但实体类中没有: ${extraTables.length} 个`);

    // 详细分析缺失的表
    if (missingTables.length > 0) {
      console.log('\n❌ 缺失的表详情:');
      missingTables.forEach(item => {
        console.log(`   ${item.entity} -> ${item.table}`);
      });
    }

    // 详细分析多余的表
    if (extraTables.length > 0) {
      console.log('\n⚠️  多余的表详情:');
      extraTables.forEach(table => {
        console.log(`   ${table}`);
      });
    }

    // 检查外键约束
    console.log('\n🔍 检查外键约束:');
    const [constraints] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        CONSTRAINT_NAME,
        COLUMN_NAME,
        REFERENCED_TABLE_NAME,
        REFERENCED_COLUMN_NAME
      FROM information_schema.KEY_COLUMN_USAGE 
      WHERE TABLE_SCHEMA = 'nextjs' 
        AND REFERENCED_TABLE_NAME IS NOT NULL
      ORDER BY TABLE_NAME, CONSTRAINT_NAME
    `);

    console.log(`📋 当前共有 ${constraints.length} 个外键约束:`);
    constraints.forEach(constraint => {
      console.log(`   ${constraint.TABLE_NAME}.${constraint.COLUMN_NAME} -> ${constraint.REFERENCED_TABLE_NAME}.${constraint.REFERENCED_COLUMN_NAME}`);
    });

    // 生成报告
    const report = {
      timestamp: new Date().toISOString(),
      summary: {
        totalEntities: Object.keys(entityMappings).length,
        totalDbTables: dbTables.length,
        existingMappings: existingTables.length,
        missingTables: missingTables.length,
        extraTables: extraTables.length,
        foreignKeyConstraints: constraints.length
      },
      existingMappings: existingTables,
      missingTables: missingTables,
      extraTables: extraTables,
      foreignKeyConstraints: constraints
    };

    fs.writeFileSync('./entity-table-mapping-report.json', JSON.stringify(report, null, 2));
    console.log('\n📄 详细报告已保存到 entity-table-mapping-report.json');

  } catch (error) {
    console.error('❌ 执行过程中发生错误:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

checkEntityTableMapping();
